package net.wicp.tams.common.apiext;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.ArrayUtils;

/***
 * jdbc工具类，纯jdbc没有连接池
 * 
 * @author zhoujunhui
 *
 */
@Slf4j
public abstract class JdbcUtil {

	public static Connection getConnection(String driverName, String dburl, String userName, String pwd) {
		Connection conn = null;
		try {
			Class.forName(driverName);
			if (StringUtil.isNull(userName) || StringUtil.isNull(pwd)) {
				conn = DriverManager.getConnection(dburl);
			} else {
				conn = DriverManager.getConnection(dburl, userName, pwd);
			}
			return conn;
		} catch (Exception e) {
			log.error(String.format("连接错误，地址[%s]", dburl), e);
		}
		return null;
	}

	public static Connection getConnection(String driverName, String dburl) {
		return getConnection(driverName, dburl, null, null);
	}

	public static ResultSet querySql(Connection conn, String sql) {
		Statement stmt = null;
		try {
			if (conn == null || conn.isClosed()) {
				return null;
			}
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			return rs;
		} catch (SQLException e) {
			log.error(String.format("查询sql出错，sql:[%s]", sql), e);
		}
		return null;
	}

	/***
	 * 设置PreparedStatement的参数
	 * 
	 * @param stmt
	 *            要设置参数的PreparedStatement
	 * @param queryParams
	 *            要设置的参数，与stmt出现的问题要对应的上
	 * @throws SQLException
	 *             sql异常
	 */
	public static void setPreParam(PreparedStatement stmt, Object... queryParams) throws SQLException {
		if (stmt == null) {
			return;
		}
		int needparamscount = stmt.getParameterMetaData().getParameterCount();
		if (needparamscount == 0 && ArrayUtils.isEmpty(queryParams)) {
			return;
		}
		if (needparamscount != queryParams.length) {
			throw new SQLException(
					String.format("参数的个数不匹配，需要[%s]个参数，但传进来[%s]个参数", needparamscount, queryParams.length));
		}
		for (int i = 0; i < queryParams.length; i++) {
			Object queryParam = queryParams[i];
			String valueStr = String.valueOf(queryParam);
			if (queryParam instanceof String) {
				stmt.setString(i + 1, valueStr);
			} else if (queryParam instanceof Integer) {
				stmt.setInt(i + 1, Integer.parseInt(valueStr));
			} else if (queryParam instanceof Date) {
				stmt.setDate(i + 1, new java.sql.Date(((Date) queryParam).getTime()));
			} else if (queryParam instanceof Long) {
				stmt.setLong(i + 1, Long.parseLong(valueStr));
			} else if (queryParam instanceof Boolean) {
				stmt.setBoolean(i + 1, Boolean.parseBoolean(valueStr));
			} else {
				throw new SQLException(String.format("不支持的类型,参数值[%s]", valueStr));
			}
		}

	}

	public static List<Map<String, String>> querySqlMap(Connection conn, String sql) {
		ResultSet rs = querySql(conn, sql);
		List<Map<String, String>> retlist = new ArrayList<>();
		try {
			int nums = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				Map<String, String> retmap = new HashMap<>();
				for (int i = 0; i < nums; i++) {
					String colName = rs.getMetaData().getColumnLabel(i + 1);
					String value = rs.getString(colName);
					retmap.put(colName, value);
				}
				retlist.add(retmap);
			}
			return retlist;
		} catch (Exception e) {
			log.error(String.format("查询sql出错，sql:[%s]", sql), e);
			return null;
		}
	}

}
